package com.mtconnect.util;

import com.mtconnect.exception.JDBCException;
import com.mtconnect.exception.ResourceLoadException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.Closeable;
import java.sql.*;
import java.util.Properties;

import static com.mtconnect.util.ResourceLoad.getResources;

/**
 * Created by Administrator on 2017/7/28.
 * 数据库连接、事务类
 */
public class JDBCTemplete implements Closeable{

    private static final Logger LOGGER = LoggerFactory.getLogger(JDBCTemplete.class);

    public static final String URL_PREFIX = "jdbc:empress://";
    public static final String SEPARATOR = ";";
    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;


    public JDBCTemplete() {
        this.connection = this.createConnection();
    }

    public void close()  {
        try {
            close(resultSet, statement, connection);
        } catch (JDBCException e) {
            e.printStackTrace();
        }
    }

    /**
     * 释放数据库连接资源
     *
     * @param resultSet  数据库结果集
     * @param statement  数据库声明
     * @param connection 数据库连接
     * @throws JDBCException 当某个以上某个对象关闭发生错误时，抛出此异常。
     */
    public void close(ResultSet resultSet, Statement statement, Connection connection) throws JDBCException {
        try {
            if (null != resultSet) {
                resultSet.close();
            }
            if (null != statement) {
                statement.close();
            }
            if (null != connection) {
                connection.close();
            }
        } catch (SQLException e) {
            if (null != connection) {//当非connection异常发生时，确保connection关闭。
                try {
                    connection.close();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            throw new JDBCException(e);
        }
    }

    /**
     * 获取DB连接用url
     *
     * @param properties 存储有数据的资源对象
     * @return 完整的url
     */
    private String getUrl(Properties properties) {
        String user = properties.getProperty("USER");
        StringBuffer sb = new StringBuffer();
        sb.append(URL_PREFIX);
        sb.append("SERVER=");
        sb.append(properties.getProperty("SERVER", "NONE"));
        sb.append(SEPARATOR);
        sb.append("PORT=");
        sb.append(properties.getProperty("PORT", "6322"));
        sb.append(SEPARATOR);
        sb.append("DATABASE=");
        sb.append(properties.getProperty("DATABASE"));
        sb.append(SEPARATOR);
        if (!"".equals(user)) {
            sb.append("USER=");
            sb.append(user);
            sb.append(SEPARATOR);
            sb.append("PASSWORD=");
            sb.append(properties.getProperty("PASSWORD"));
            sb.append(SEPARATOR);
        } else {
            sb.append("UID=");
            sb.append(properties.getProperty("UID"));
            sb.append(SEPARATOR);
            sb.append("PWD=");
            sb.append(properties.getProperty("PWD"));
            sb.append(SEPARATOR);
        }
        sb.append("TIMEOUT_OPEN=");
        sb.append(properties.getProperty("TIMEOUT_OPEN", "10"));
        sb.append(SEPARATOR);
        sb.append("TIMEOUT_NORMAL=");
        sb.append(properties.getProperty("TIMEOUT_NORMAL", "3600"));
        sb.append(SEPARATOR);
        return sb.toString();
    }

    /**
     * 获取connection
     *
     * @return 一个connection
     */
    public Connection createConnection() {
        String url = null;
        try {
            url = getUrl(getResources(null));
            Class.forName("com.empress.jdbc.empressDriver");
            this.connection = DriverManager.getConnection(url);
            return this.connection;
        } catch (ClassNotFoundException e) {
            LOGGER.error(" Not Found com.empress.jdbc.empressDriver", e);
        } catch (SQLException e) {
            LOGGER.error("Connection error", e);
            LOGGER.error("Connection param is {}", url);
        } catch (ResourceLoadException e) {
            LOGGER.error("not load configuration file", e);
        }
        return null;
    }

    /**
     * 开始事务
     *
     * @throws JDBCException if a database access error occurs,
     *                       this method is called on a closed connection
     */
    public void startTransaction() throws JDBCException {
        try {
            this.connection.setAutoCommit(false);
        } catch (SQLException e) {
            throw new JDBCException(e);
        }
    }

    /**
     * 结束事务
     *
     * @throws JDBCException if a database access error occurs,
     *                       setAutoCommit(true) is called while participating in a distributed transaction,
     *                       or this method is called on a closed connection
     */

    public void endTransaction() throws JDBCException {
        try {
            this.connection.setAutoCommit(true);
        } catch (SQLException e) {
            throw new JDBCException(e);
        }
    }

    /**
     * 提交事务
     *
     * @throws JDBCException if a database access error occurs,
     *                       setAutoCommit(true) is called while participating in a distributed transaction,
     *                       or this method is called on a closed connection
     */
    public void commit() throws JDBCException {
        try {
            this.connection.commit();
        } catch (SQLException e) {
            throw new JDBCException(e);
        }
    }

    /**
     * 回滚事务
     *
     * @throws JDBCException if a database access error occurs,
     *                       setAutoCommit(true) is called while participating in a distributed transaction,
     *                       or this method is called on a closed connection
     */
    public void rollback() throws JDBCException {
        try {
            this.connection.rollback();
        } catch (SQLException e) {
            throw new JDBCException(e);
        }
    }

    public ResultSet executeQuery(String sql) {
        try {
            statement = connection.createStatement();
            return statement.executeQuery(sql);
        } catch (SQLException e) {
            LOGGER.warn("execute query sql error", e);
            return null;
        }
    }

    public boolean executeSQL(String sql) {
        try {
            statement = connection.createStatement();
            return statement.execute(sql);
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.warn("execute sql error", e);
            return false;
        }
    }

    public Connection getConnection() {
        return this.connection;
    }
}
